﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace UCOLOR.DAL.Helper
{
     public class SqliteDataHelper
    {
        // public static DbSql db = new DbSql(@"data source=SystemDB.DB3", "Sqlite");
         public static DbSql uclolordb = new DbSql(@"data source=UColor.db3", "Sqlite");

         /// <summary>
         /// 图片存放的根目录
         /// </summary>
         /// <returns></returns>
         public static String GetIMAGE_ROOT()
         {
             var dt = uclolordb.ExecuteQuery("select * from system_parameter_tab");
             return  dt.Select("param_id='IMAGE_ROOT'")[0].ItemArray[2].ToString();
             //return dt;
         }


         /// <summary>
         /// 获取参数值
         /// </summary>
         /// <param name="param"></param>
         /// <returns></returns>
         public static String GetParameterInfo(string param)
         {
             var dt = uclolordb.ExecuteQuery("select * from system_parameter_tab");
             return dt.Select(string.Format("param_id='{0}'",param))[0].ItemArray[2].ToString();
             //return dt;
         }

         /// <summary>
         /// 获取所有二级分类
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetPartProductFamilyTab()
         {
             var dt = uclolordb.ExecuteQuery("select * from part_product_family_tab");
             return dt;
         }

         /// <summary>
         /// 获取二级分类
         /// </summary>
         /// <param name="product_code"></param>
         /// <returns></returns>
         public static System.Data.DataTable GetPartProductFamilyTab(string product_code = "")
         {
             string sql = string.Format("select * from part_product_family_tab where product_code='{0}'", product_code);
             if (string.IsNullOrEmpty(product_code))
                 sql = "select * from part_product_family_tab where product_code";
             var dt = uclolordb.ExecuteQuery(string.Format(sql,product_code));
             return dt;
         }

         /// <summary>
         /// 一级分类
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetPartProductCodeTab(string product_code = "")
         {
             string sql = "select * from part_product_code_tab";
             if (!string.IsNullOrEmpty(product_code))
                 sql = string.Format("select * from part_product_code_tab where product_code ='{0}'", product_code);
             var dt = uclolordb.ExecuteQuery(sql);
             return dt;
         }
         /// <summary>
         /// 取得产品关联的模板
         /// </summary>
         /// <param name="catalog_no"></param>
         /// <returns></returns>
         public static System.Data.DataTable GetSalesCatalogTemplateTab(string catalog_no = "")
         {
             string sql = "select * from sales_catalog_template_tab";
             if (!string.IsNullOrEmpty(catalog_no))
                 sql = string.Format("select * from part_product_code_tab where product_code ='{0}'", catalog_no);
             var dt = uclolordb.ExecuteQuery(sql);
             return dt;
         }
         /// <summary>
         /// 产品属性
         /// </summary>
         /// <param name="catalog_no"></param>
         /// <returns></returns>
         public static System.Data.DataTable GetSalesPartTab(string catalog_no = "")
         {
             string sql = "select * from sales_part_tab where catalog_no='"+catalog_no+"'";
    
             var dt = uclolordb.ExecuteQuery(sql);
             return dt;
         }
         /// <summary>
         /// 产品属性
         /// </summary>
         /// <param name="catalog_no"></param>
         /// <returns></returns>
         public static System.Data.DataTable GetSalesPartTabWhere(string Where)
         {
             
             string sql = "select * from sales_part_tab ";
             if (!string.IsNullOrEmpty(Where))
                 sql = sql + "where " + Where;

             var dt = uclolordb.ExecuteQuery(sql);
             return dt;
         }
         public static System.Data.DataTable GetSalesPartTab(string product_code = "",string product_family="")

         {
             string sql = "select * from sales_part_tab";
             string where ="";
             if (!string.IsNullOrEmpty(product_code))
                 where = "product_code='" + product_code + "'";
             if (!string.IsNullOrEmpty(product_family))
             {
                 if (!string.IsNullOrEmpty(where))
                     where = where + " and  product_family='" + product_family + "'";
                 else
                     where = "product_family='" + product_family + "'";
             }
             if (!string.IsNullOrEmpty(where))
                 sql = sql + " where " + where;
             var dt = uclolordb.ExecuteQuery(sql);
             return dt;
         }

          
         /// <summary>
         /// 获取模版类型
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetSalesTemplateTypeTab()
         {
             var dt = uclolordb.ExecuteQuery("select * from sales_template_type_tab");
             return dt;
         }

         /// <summary>
         /// 根据产品获取模版类型
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetSalesTemplateTypeTab(string catalog_no)
         {
             string sql = string.Format(@"select * from sales_template_type_tab where template_type
 in (select template_type from sales_template_tab st,sales_catalog_template_tab sc 
 where st.template_id=sc.template_id and sc.catalog_no='{0}')",catalog_no);
             var dt = uclolordb.ExecuteQuery(sql);
             return dt;
         }
         /// <summary>
         /// 获取产品分类模版
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetSalesTemplateTab(string template_type,bool UseWhere = false)
         {
             System.Data.DataTable dt;
             if (!UseWhere)
             {
                 if (String.IsNullOrEmpty(template_type))
                 {
                     dt = uclolordb.ExecuteQuery(string.Format("select * from  sales_template_tab", template_type));
                 }
                 else
                 {
                     dt = uclolordb.ExecuteQuery(string.Format("select * from  sales_template_tab where template_type='{0}'", template_type));
                 }
             }
             else
             {
                 dt = uclolordb.ExecuteQuery(string.Format("select * from  sales_template_tab where {0}", template_type));
             }
             return dt;
         }
        

         /// <summary>
         /// 获取产品对应的模版数据
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetSalesTemplatePageTab(string template_id)
         {
             var dt = uclolordb.ExecuteQuery(string.Format("select * from   sales_template_page_tab where template_id='{0}'",template_id));
             return dt;
         }


         /// <summary>
         /// 所有模版分类数据
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetSalesCatalogTemplateTab()
         { 
             var dt = uclolordb.ExecuteQuery("select * from   sales_catalog_template_tab");
             return dt;
         }

         /// <summary>
         ///  分组
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetExhibitionGroupTab()
         {
             var dt = uclolordb.ExecuteQuery("select * from   exhibition_group_tab");
             return dt;
         }


        /// <summary>
        ///  分组项目信息
        /// </summary>
        /// <returns></returns>
         public static System.Data.DataTable GetExhibitionGroupItemTab(string groupid)
         {
             var dt = uclolordb.ExecuteQuery(string.Format("select * from  exhibition_group_item_tab where group_id='{0}'", groupid));
             return dt;
         }

         /// <summary>
         ///  分组项目信息
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetExhibitionGroupItemTabByCatalog(string catalog_no)
         {
             var dt = uclolordb.ExecuteQuery(string.Format("select * from  exhibition_group_item_tab where catalog_no='{0}'", catalog_no));
             return dt;
         }

         //public static System.Data.DataTable GetExhibitionGroupItemTab( string groupid)
         //{
         //    var dt = uclolordb.ExecuteQuery("select * from   exhibition_group_item_tab where group_id='" + groupid + "'");
         //    return dt;
         //}
         /// <summary>
         /// 
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetShoppingCartTab()
         {
             var dt = uclolordb.ExecuteQuery("select * from   Shopping_Cart_Tab");
             return dt;
         }

         /// <summary>
         /// 获取消息
         /// </summary>
         /// <returns></returns>
         public static System.Data.DataTable GetClientMessageTab()
         {
             var dt = uclolordb.ExecuteQuery("select * from   client_message_tab");
             return dt;
         }

         public static System.Data.DataTable GetSalesPartAssist(string catalog_no)
         {
             string sql = "select * from sales_part_assist_tab where catalog_no ='" + catalog_no + "'";
             var dt = uclolordb.ExecuteQuery(sql);
             return dt;
         }
         




//         public static System.Data.DataTable GetCategory()
//         {
//             // ZX.BLL.Extend.BaseBussnies bll = new BLL.Extend.BaseBussnies();
//             //DbSql db = new DbSql(@"data source=SystemDB.DB3", "Sqlite");
//             var dt = db.ExecuteQuery("select * from Category");
//             return dt;
//         }

//         public static System.Data.DataTable GetCategory(string categoryNo)
//         {
//             //DbSql db = new DbSql(@"data source=SystemDB.DB3", "Sqlite");
//             var dt = db.ExecuteQuery(string.Format(@"SELECT   ID, Category, ParentID, CoverPage, Path
//FROM      Category
//WHERE   (ParentID =
//                    (SELECT   ID
//                     FROM      Category Category_1
//                     WHERE   (Category = '{0}')))",categoryNo));
//             return dt;
//         }


//         public static System.Data.DataTable GetProducts(string categoryNo)
//         {
//             //DbSql db = new DbSql(@"data source=SystemDB.DB3", "Sqlite");
//             var dt = db.ExecuteQuery(string.Format(@"SELECT   ID, CategoryID, ProductName, CoverPage, Price, Notes
//FROM      Product
//WHERE   (CategoryID =
//                    (SELECT   ID
//                     FROM      Category Category_1
//                     WHERE   (Category = '{0}')))", categoryNo));
//             return dt;
//         }
//         public static System.Data.DataTable GetUserCarts(string categoryNo)
//         {
//             //DbSql db = new DbSql(@"data source=SystemDB.DB3", "Sqlite");
//             var dt = db.ExecuteQuery(string.Format(@"select * from V_UserCart", categoryNo));
//             return dt;
//         }

//         public static System.Data.DataTable GetOrderProducts(string orderID)
//         {
//             //DbSql db = new DbSql(@"data source=SystemDB.DB3", "Sqlite");
//             var dt = db.ExecuteQuery(string.Format(@"SELECT   ID, CategoryID, ProductName, CoverPage, Price, Notes
//FROM      Product
//WHERE   (CategoryID =
//                    (SELECT   ID
//                     FROM      Category Category_1
//                     WHERE   (Category = '{0}')))", orderID));
//             return dt;
//         }

//         public static System.Data.DataTable GetProductByID(int productID)
//         {
//             //DbSql db = new DbSql(@"data source=SystemDB.DB3", "Sqlite");
//             var dt = db.ExecuteQuery(string.Format(@"SELECT   ID, CategoryID, ProductName, CoverPage, Price, Notes
//FROM      Product
//WHERE   ID = '{0}'", productID));
//             return dt;
//         }


//         public static System.Data.DataTable GetProductPictureByID(int productID)
//         {
//             //DbSql db = new DbSql(@"data source=SystemDB.DB3", "Sqlite");
//             var dt = db.ExecuteQuery(string.Format(@"SELECT   ID, ProductID,Picture, Notes
//FROM      ProductPicture
//WHERE   ProductID = '{0}'", productID));
//             return dt;
//         }  
        
    }
}
